﻿using System;
using System.Data.Odbc;
using System.Windows.Forms;

namespace AccessODBC_GUI
{
    public partial class AccessOdbcForm : Form
    {
        private OdbcConnection _odbcConnection;
        private OdbcCommand _odbcCommand;

        public AccessOdbcForm()
        {
            InitializeComponent();
        }

        private void AccessOdbcFormLoad(object sender, EventArgs e)
        {
            employeeTableAdapter.Fill(dataSetAccess.Employee);
            departmentsTableAdapter.Fill(dataSetAccess.Departments);

        }

        private void RefreshTreeViewClick(object sender, EventArgs e)
        {
            progressBarTreeView.Value = 0;
            treeViewDepartments.Nodes.Clear();
            _odbcConnection = new OdbcConnection(Properties.Settings.Default.ConnectionString);
            _odbcConnection.Open();
            _odbcCommand = new OdbcCommand("Select Id, City From Departments", _odbcConnection);
            progressBarTreeView.PerformStep();
            TreeNode rootNode;
            using (var odbcDataReaderDepartments = _odbcCommand.ExecuteReader())
            {
                rootNode = new TreeNode("Departments");
                TreeNode cityNode;
                while (odbcDataReaderDepartments.Read())
                {
                    cityNode = new TreeNode((string)odbcDataReaderDepartments[1]);
                    _odbcCommand = new OdbcCommand("Select FirstName, LastName From Employee WHERE fk_Departments_Id = ?", _odbcConnection);
                    _odbcCommand.Parameters.AddWithValue("@p1", odbcDataReaderDepartments[0]);
                    using (var odbcDataReaderEmployees = _odbcCommand.ExecuteReader())
                    {
                        while (odbcDataReaderEmployees.Read())
                        {
                            cityNode.Nodes.Add((string)odbcDataReaderEmployees[0] + ", " + (string)odbcDataReaderEmployees[1]);
                        }
                    }
                    rootNode.Nodes.Add(cityNode);
                    if (progressBarTreeView.Value < 90) progressBarTreeView.Value += 2;
                }
            }
            progressBarTreeView.Value = 90;
            rootNode.Expand();
            treeViewDepartments.Nodes.Add(rootNode);
            _odbcConnection.Close();
            progressBarTreeView.PerformStep();
        }

        private void AccessOdbcFormClosing(object sender, FormClosingEventArgs e)
        {
            departmentsTableAdapter.Connection.Close();
            employeeTableAdapter.Connection.Close();
        }

        private static void MenuExitClick(object sender, EventArgs e)
        {
            Environment.Exit(0);
        }
    }
}
